package com.carlyang.studentinfo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.carlyang.studentinfo.model.Class;
import com.carlyang.studentinfo.model.PageBean;
import com.carlyang.studentinfo.util.LogUtil;
import com.carlyang.studentinfo.util.StringUtil;

public class ClassDao {
	public static final String TAG = "ClassDao";
	
	public ResultSet getClassList(Connection con,PageBean pageBean,Class class1)throws Exception{
		ResultSet resultSet = null;
		//select * from t_class where className like "%xx%" limit start,num
		StringBuilder msq = new StringBuilder("select * from t_class");
		//如果有传入的class1的name不为空，则需要添加查询条件；如果为空，则不会执行这句。
		if (class1 !=null && StringUtil.isNotEmpty(class1.getClassName())) {
			msq.append(" and className like '%"+class1.getClassName()+"%'");
		}
		if (pageBean!=null) {
			msq.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
		}
		LogUtil.i(TAG, "getClassList SQL语句:"+msq.toString().replaceFirst("and", "where"));
		PreparedStatement pstmt = con.prepareStatement(msq.toString().replaceFirst("and", "where"));
		resultSet = pstmt.executeQuery();
		return resultSet;
	}
	
	public int getClassCount(Connection con,Class class1) throws Exception{
		StringBuilder sb = new StringBuilder("select count(*) as total from t_class");
		//如果有传入的class1的name不为空，则需要添加查询条件；如果为空，则不会执行这句。
		if (StringUtil.isNotEmpty(class1.getClassName())) {
			sb.append(" and className like '%"+class1.getClassName()+"%'");
		}
		LogUtil.i(TAG, "getClassCount SQL语句:"+sb.toString().replaceFirst("and", "where"));
		PreparedStatement pstmt = con.prepareStatement(sb.toString().replaceFirst("and", "where"));
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt("total");
		}else {
			return 0;
		}
	}
	
	/**
	 * delete from tableName where field in (1,3,5)
	 * @param con
	 * @param delIds
	 * @return
	 */
	public int deleteClass(Connection con,String delIds) throws Exception{
		String sql="delete from t_class where id in ("+delIds+")";
		LogUtil.i(TAG, "deleteClass SQL语句:"+sql);
		PreparedStatement pstmt = con.prepareStatement(sql);
		return pstmt.executeUpdate();
	}
	/**
	 * 向数据库t_class表插入一条数据：inseert into t_class values(null,?,?)
	 * @param con
	 * @param class1
	 * @return
	 * @throws Exception
	 */
	public int addClass(Connection con,Class class1) throws Exception{
		String sql = "insert into t_class values(null,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, class1.getClassName());
		pstmt.setString(2, class1.getClassDesc());
		LogUtil.i(TAG, "addClass SQL语句:"+pstmt.toString());
		return pstmt.executeUpdate();
	}
	
	public int updateClass(Connection con,Class class1) throws Exception{
		String sql = "update t_class set className=?,classDesc=? where id=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, class1.getClassName());
		pstmt.setString(2, class1.getClassDesc());
		pstmt.setInt(3, class1.getId());
		LogUtil.i(TAG, "updateClass SQL语句:"+pstmt.toString());
		return pstmt.executeUpdate();
	}
}
